﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Fasterflect;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Web.Configuration;

namespace VVA.Website.UI.Helper
{
    public class DataHelper
    {
        #region "Declaration"

        private static DataHelper _helper;
        private SqlDatabase _db;

        #endregion

        #region Constructor

        public DataHelper()
        {
            _db = new SqlDatabase(WebConfigurationManager.ConnectionStrings["camchimvva"].ConnectionString);
        }

        #endregion

        #region Method

        public static DataHelper CreateInstance()
        {
            if (_helper == null)
            {
                _helper = new DataHelper();
            }
            return _helper;
        }

        private SqlCommand CreateCommand(string storedProcedureName, params object[] values)
        {
            return (SqlCommand)_db.GetStoredProcCommand(storedProcedureName, values);
        }

        private SqlCommand CreateCommand(string storedProcedureName, object obj)
        {
            var cmd = _db.GetStoredProcCommand(storedProcedureName);
            _db.DiscoverParameters(cmd);

            for (int i = 0; i < cmd.Parameters.Count; i++)
            {
                var param = cmd.Parameters[i];

                if (param.Direction == System.Data.ParameterDirection.Input)
                {
                    param.Value = obj.GetPropertyValue(param.ParameterName.Replace("@", ""));
                }
            }

            return (SqlCommand)cmd;
        }

        public int ExecuteNonQuery(string storedProcedureName, object obj)
        {
            return _db.ExecuteNonQuery(CreateCommand(storedProcedureName, obj));
        }

        public object ExecuteScalar(string storedProcedureName, object obj)
        {
            return _db.ExecuteScalar(CreateCommand(storedProcedureName, obj));
        }

        public IDataReader ExecuteReader(string storedProcedureName, object obj)
        {
            return _db.ExecuteReader(CreateCommand(storedProcedureName, obj));
        }

        public IList<T> ExecuteCollection<T>(string storedProcedureName, object obj) where T : class, new()
        {
            var reader = ExecuteReader(storedProcedureName, obj);
            var lst = new List<T>();

            while (reader.Read())
            {
                var newItem = new T();
                var properties = newItem.GetType().GetProperties();

                for (int i = 0; i < properties.Count(); i++)
                {
                    try
                    {
                        if (reader[properties[i].Name] != null)
                        {
                            newItem.TrySetValue(properties[i].Name, reader[properties[i].Name]);
                        }
                    }
                    catch
                    {
                        continue;
                    }
                }

                lst.Add(newItem);
            }

            return lst;
        }

        #endregion
    }
}